package cn.lanqiao.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import cn.lanqiao.entity.Student;

@RestController
//跨域
@CrossOrigin(origins="*",maxAge=3600)
public class StudentExcelController {
	private cn.lanqiao.service.StudentService studentservice;
	@RequestMapping(value = "/export")
	@ResponseBody
	public void export(HttpServletResponse response) throws IOException {
		List<Student> students = studentservice.selectStudent();

		HSSFWorkbook wb = new HSSFWorkbook();

		HSSFSheet sheet = wb.createSheet("获取excel测试表格");

		HSSFRow row = null;

		row = sheet.createRow(0);//创建第一个单元格
		row.setHeight((short) (26.25 * 20));
		row.createCell(0).setCellValue("用户信息列表");//为第一行单元格设值

		/*为标题设计空间
		* firstRow从第1行开始
		* lastRow从第0行结束
		*
		*从第1个单元格开始
		* 从第3个单元格结束
		*/
		CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 2);
		sheet.addMergedRegion(rowRegion);

		/*CellRangeAddress columnRegion = new CellRangeAddress(1,4,0,0);
		sheet.addMergedRegion(columnRegion);*/


		/*
		* 动态获取数据库列 sql语句 select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name='user' and table_schema='test'
		* 第一个table_name 表名字
		* 第二个table_name 数据库名称
		* */
		row = sheet.createRow(1);
		row.setHeight((short) (22.50 * 20));//设置行高
		row.createCell(0).setCellValue("用户Id");//为第一个单元格设值
		row.createCell(1).setCellValue("用户名");//为第二个单元格设值
		row.createCell(2).setCellValue("年级");//为第三个单元格设值
		row.createCell(3).setCellValue("专业");//为第四个单元格设值
		row.createCell(4).setCellValue("班级");//为第5个单元格设值
		row.createCell(5).setCellValue("学院");//为第6个单元格设值
		row.createCell(6).setCellValue("性别");//为第7个单元格设值
		row.createCell(7).setCellValue("电话号码");//为第8个单元格设值
		row.createCell(8).setCellValue("邮箱");//为第9个单元格设值
		for (int i = 0; i < students.size(); i++) {
			row = sheet.createRow(i + 2);
			Student student = students.get(i);
			row.createCell(0).setCellValue(student.getStudentId());
			row.createCell(1).setCellValue(student.getStudentName());
			row.createCell(2).setCellValue(student.getGrade());
			row.createCell(3).setCellValue(student.getMajor());
			row.createCell(4).setCellValue(student.getStudentClass());
			row.createCell(5).setCellValue(student.getInstitute());
			row.createCell(6).setCellValue(student.getSex());
			row.createCell(7).setCellValue(student.getTel());
			row.createCell(8).setCellValue(student.getEmail());
		}
		sheet.setDefaultRowHeight((short) (16.5 * 20));
		//列宽自适应
		for (int i = 0; i <= 13; i++) {
			sheet.autoSizeColumn(i);
		}

		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		OutputStream os = response.getOutputStream();
		response.setHeader("Content-disposition", "attachment;filename=user.xls");//默认Excel名称
		wb.write(os);
		os.flush();
		os.close();


	}
}
